insert overwrite table jms_dm.dm_duration_of_whole_process_pre_wt
select
      next_day(date_add(sign_date,-1*7), 'Monday') as monday_date
     ,concat(year(date_sub(next_day(sign_date,'MO'),4)),'-',if(weekofyear(sign_date)<10,concat(0,weekofyear(sign_date)),weekofyear(sign_date))) as sign_date   --时效签收日期'
     ,send_network_code                      as send_network_code       --始发网点编码
     ,send_network_name                      as send_network_name       --始发网点名称
     ,sign_network_code                      as sign_network_code       --签收网点编码
     ,sign_network_name                      as sign_network_name       --签收网点名称
     ,send_area_id                           as send_area_id            --始发区县id
     ,send_area_desc                         as send_area_desc          --始发区县名称
     ,send_city_id                           as send_city_id            --始发城市id
     ,send_city_desc                         as send_city_desc          --始发城市名称
     ,send_agent_code                        as send_agent_code         --始发代理区code
     ,send_agent_name                        as send_agent_name         --始发代理区名称
     ,send_fran_code                         as send_fran_code          --始发加盟商code
     ,send_fran_name                         as send_fran_name          --始发加盟商名称
     ,send_provider_id                       as send_provider_id        --始发省份id
     ,send_provider_desc                     as send_provider_desc      --始发省份名称
     ,send_regional_id                       as send_regional_id        --始发大区id
     ,send_regional_desc                     as send_regional_desc      --始发大区名称
     ,sign_area_id                           as sign_area_id            --签收区县id
     ,sign_area_desc                         as sign_area_desc          --签收区县名称
     ,sign_city_id                           as sign_city_id            --签收城市id
     ,sign_city_desc                         as sign_city_desc          --签收城市名称
     ,sign_agent_code                        as sign_agent_code         --签收代理区code
     ,sign_agent_name                        as sign_agent_name         --签收代理区名称
     ,sign_fran_code                         as sign_fran_code          --签收加盟商code
     ,sign_fran_name                         as sign_fran_name          --签收加盟商名称
     ,sign_provider_id                       as sign_provider_id        --签收省份id
     ,sign_provider_desc                     as sign_provider_desc      --签收省份名称
     ,sign_regional_id                       as sign_regional_id        --签收大区id
     ,sign_regional_desc                     as sign_regional_desc      --签收大区名称
     ,order_source_id                        as order_source_id         --订单来源id
     ,ordersource_code                       as ordersource_code        --订单来源编码
     ,ordersource_name                       as ordersource_name        --订单来源名称
     ,deliver_later_user_code                as deliver_later_user_code --出仓员编码 
     ,deliver_later_user                     as deliver_later_user      --出仓员名称
     ,sum(pre_sign_taking_time             ) as pre_sign_taking_time
     ,sum(real_sign_taking_time            ) as real_sign_taking_time
     ,sum(first_center_taking_time         ) as first_center_taking_time
     ,sum(inout_first_center_time          ) as inout_first_center_time
     ,sum(dest_first_center_time           ) as dest_first_center_time
     ,sum(last_first_center_time           ) as last_first_center_time
     ,sum(inout_last_center_time           ) as inout_last_center_time
     ,sum(dest_last_center_time            ) as dest_last_center_time
     ,sum(pre_sign_dest_time               ) as pre_sign_dest_time
     ,sum(real_pre_sign_time               ) as real_pre_sign_time
     ,sum(network_taking_send_time         ) as network_taking_send_time
     ,sum(network_send_nodal_arrival_time  ) as network_send_nodal_arrival_time
     ,sum(first_nodal_arrival_send_time    ) as first_nodal_arrival_send_time
     ,sum(nodal_send_center_arrival_time   ) as nodal_send_center_arrival_time
     ,sum(nodal_arrival_center_send_time   ) as nodal_arrival_center_send_time
     ,sum(end_nodal_arrival_send_time      ) as end_nodal_arrival_send_time
     ,sum(nodal_send_network_arrival_time  ) as nodal_send_network_arrival_time
     ,sum(end_network_arrival_deliver_time ) as end_network_arrival_deliver_time
     ,sum(deliver_aging_sign_time          ) as deliver_aging_sign_time
     ,sum(actual_aging_sign_time           ) as actual_aging_sign_time
     ,sum(pre_sign_taking_cnt              ) as pre_sign_taking_cnt
     ,sum(real_sign_taking_cnt             ) as real_sign_taking_cnt
     ,sum(first_center_taking_cnt          ) as first_center_taking_cnt
     ,sum(inout_first_center_cnt           ) as inout_first_center_cnt
     ,sum(dest_first_center_cnt            ) as dest_first_center_cnt
     ,sum(last_first_center_cnt            ) as last_first_center_cnt
     ,sum(inout_last_center_cnt            ) as inout_last_center_cnt
     ,sum(dest_last_center_cnt             ) as dest_last_center_cnt
     ,sum(pre_sign_dest_cnt                ) as pre_sign_dest_cnt
     ,sum(real_pre_sign_cnt                ) as real_pre_sign_cnt
     ,sum(network_taking_send_cnt          ) as network_taking_send_cnt
     ,sum(network_send_nodal_arrival_cnt   ) as network_send_nodal_arrival_cnt
     ,sum(first_nodal_arrival_send_cnt     ) as first_nodal_arrival_send_cnt
     ,sum(nodal_send_center_arrival_cnt    ) as nodal_send_center_arrival_cnt
     ,sum(nodal_arrival_center_send_cnt    ) as nodal_arrival_center_send_cnt
     ,sum(end_nodal_arrival_send_cnt       ) as end_nodal_arrival_send_cnt
     ,sum(nodal_send_network_arrival_cnt   ) as nodal_send_network_arrival_cnt
     ,sum(end_network_arrival_deliver_cnt  ) as end_network_arrival_deliver_cnt
     ,sum(deliver_aging_sign_cnt           ) as deliver_aging_sign_cnt
     ,sum(actual_aging_sign_cnt            ) as actual_aging_sign_cnt
     ,sum(if_deliver_24h_cnt               ) as if_deliver_24h_cnt  --派件-时效签收超24H量
     ,concat(year(date_sub(next_day(sign_date,'MO'),4)),'-',if(weekofyear(sign_date)<10,concat(0,weekofyear(sign_date)),weekofyear(sign_date))) as dt
from jms_dm.dm_duration_of_whole_process_pre_dt
where dt >=date_add(date_add('{{ execution_date | date_add(1) | cst_ds }}',-14),1 - case when dayofweek(date_add('{{ execution_date | date_add(1) | cst_ds }}',-14)) = 1 then 7 else dayofweek(date_add('{{ execution_date | date_add(1) | cst_ds }}',-14)) - 1 end)
  and dt < date_add('{{ execution_date | date_add(1) | cst_ds }}',1 - case when dayofweek('{{ execution_date | date_add(1) | cst_ds }}') = 1 then 7 else dayofweek('{{ execution_date | date_add(1) | cst_ds }}') - 1 end)
group by next_day(date_add(sign_date,-1*7), 'Monday')
       ,concat(year(date_sub(next_day(sign_date,'MO'),4)),'-',if(weekofyear(sign_date)<10,concat(0,weekofyear(sign_date)),weekofyear(sign_date)))--时效签收日期'
       ,send_network_code           --始发网点编码
       ,send_network_name           --始发网点名称
       ,sign_network_code           --签收网点编码
       ,sign_network_name           --签收网点名称
       ,send_area_id                --始发区县id
       ,send_area_desc              --始发区县名称
       ,send_city_id                --始发城市id
       ,send_city_desc              --始发城市名称
       ,send_agent_code             --始发代理区code
       ,send_agent_name             --始发代理区名称
       ,send_fran_code              --始发加盟商code
       ,send_fran_name              --始发加盟商名称
       ,send_provider_id            --始发省份id
       ,send_provider_desc          --始发省份名称
       ,send_regional_id            --始发大区id
       ,send_regional_desc          --始发大区名称
       ,sign_area_id                --签收区县id
       ,sign_area_desc              --签收区县名称
       ,sign_city_id                --签收城市id
       ,sign_city_desc              --签收城市名称
       ,sign_agent_code             --签收代理区code
       ,sign_agent_name             --签收代理区名称
       ,sign_fran_code              --签收加盟商code
       ,sign_fran_name              --签收加盟商名称
       ,sign_provider_id            --签收省份id
       ,sign_provider_desc          --签收省份名称
       ,sign_regional_id            --签收大区id
       ,sign_regional_desc          --签收大区名称
       ,order_source_id             --订单来源id
       ,ordersource_code            --订单来源编码
       ,ordersource_name            --订单来源名称
       ,deliver_later_user_code     --出仓员编码 
       ,deliver_later_user          --出仓员名称
distribute by concat(year(date_sub(next_day(sign_date,'MO'),4)),'-',weekofyear(sign_date)),pmod(hash(rand()),30)
;
